package net.tngou.jtdb;

import java.io.IOException;
import java.io.StringReader;
import java.lang.reflect.InvocationTargetException;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.tngou.db.util.ResultSet;
import net.tngou.jtdb.Field.Type;
import net.tngou.jtdb.SortField.Order;
import net.tngou.jtdb.sql.Connection;
import net.tngou.jtdb.sql.DBManager;
import net.tngou.jtdb.sql.Statement;

public class TngouDBHelp {

	
	
	
	static Connection con = null;
	
	
	
	public static TngouDBHelp getConnection()  {
		con=Connection.getConnection();
		return new TngouDBHelp();
		
	}
	
	public void createTable(String tableName) {
		Statement stmt = con.createStatement();
		String sql ="create table "+tableName;
		try {
			stmt.execute(sql);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void dropTable(String tableName) {
		Statement stmt = con.createStatement();
		String sql ="drop table "+tableName;
		try {
			stmt.execute(sql);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void insert(String tableName ,Field ...fields) {
		
		Statement stmt = con.createStatement();
		String[] params =new String[fields.length];
		String fs="";
		String values="";
		for (int i = 0; i < fields.length; i++) {
			if(i!=0){fs+=",";values+=",";}
			fs+=fields[i].getName()+":"+fields[i].getType();
			values+="?";
			params[i]=fields[i].getValue();
		}
		
		String sql ="insert into "+tableName
				  +"("+fs+") "
				  + " values("+values+")";
	
//		System.out.println(sql);
		try {
			stmt.execute(sql, params);
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Page select(String tableName ,SortField sortField,int page,int size,Field ...fields) {
		
		 Page p = new Page();
		 if(page < 0 || size < 0){page=1;size=1;}
		 int from = (page - 1) * size;
		 size = (size > 0) ? size : Integer.MAX_VALUE;
		 Statement stmt = con.createStatement();
		 String sql="select * from " +tableName;
		 String[] params = null; 
		 if(fields.length>0)
		 {
			 params= new String[fields.length];
			 sql=sql+" where ";
			for (int i = 0; i < fields.length; i++) {
				if(i!=0)  sql=sql+" and ";
				sql=sql+fields[i].getName()+":"+fields[i].getType()+"=?";
				params[i]=fields[i].getValue();
			}
		 }
		 
		 if(sortField!=null)
			 sql=sql+ " order by "+sortField.getName()+":"+sortField.getType() +" "+ sortField.getOrder();
		 sql=sql+ " limit "+from+","+size;
		 System.out.println(sql);
		 ResultSet resultSet=null;
		 try {
				if(params==null)params= new String[0];
				
			     resultSet=stmt.execute(sql,params);
				BeanUtils.copyProperties(p, resultSet);
			} catch (IOException | InterruptedException | IllegalAccessException | InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return p;
		
	}
	
	
	
	
	/**
	 *  回收连接池
	* @Title: closeConnection
	* @Description: TODO(这里用一句话描述这个方法的作用)
	* @param     设定文件
	* @return void    返回类型
	* @throws
	 */
	public void closeConnection() {
		Connection.closeConnection(); // 回收链接
	}
	
	
	/**
	 * 
	* @Title: close
	* @Description: TODO关闭链接
	* @param     设定文件
	* @return void    返回类型
	* @throws
	 */
	public void close() {
		Connection.close();            // 关闭链接
	}
	
	
	
	
	public static void main(String[] args)  {
//		String tableName = "img";
//		TngouDBHelp help=getConnection();
////		help.createTable(tableName);
//		
//		Field[] fields= new Field[5];
//		fields[0]  = new Field("id", "1", Type.Key);
//		fields[1]  = new Field("newsclass", "2", Type.String);
//		fields[2]  = new Field("title", "天狗数据库", Type.Text);
//		fields[3]  = new Field("message", "天狗NoSQL数据库,全文索引！", Type.Text);
//		fields[4]  = new Field("count", "100", Type.String);
////		help.insert(tableName , fields);
//		Field wfields = new Field("tag", "news");
//		SortField sortField =new SortField("time", net.tngou.jtdb.SortField.Type.String,Order.DESC);
//		Page page = help.select(tableName, sortField , 1, 100 ,wfields );
//		System.err.println(page);
//		help.closeConnection();
//		help.close();
		            // 关闭链接
		
		String sql="insert into news(id:Key,newclass:String,newsclass:String,img:String,title:Text,message:Text,url:String)  values('170160','8','8','/news/141228/170160.jpg','2012鱼混养技术推荐'以鱼养鱼养殖新技术'','以鱼养鱼也就是把不同的鱼混养在一起，利用它们的自身特性互相利用，以达到高产目的。现介绍-些根据鱼类习性，实施以鱼养鱼的低本高产新技术。  (1)鲤鱼、鲫鱼  它们是水底“清洁工”。栖息在水体底并摄食底部的残饵和腐殖质，充分利用水体中残余的有机物和有机生物，能大大减轻池底有机物的腐化分解，减少鱼病发生。  (2)鳜鱼、乌鳢、加州鲈  它们是水体中的除杂“先锋”。其性情凶猛，能大量摄食野杂鱼、小虾等。适当套养三者之一，即可控制野杂鱼虾的生长繁殖，减少与主养鱼争食争氧的竞争压力，又可利用天然动物饵料增加产鱼量。鳜鱼、乌鳢主要采食水体中的鲜活鱼虾，而加州鲈、淡水白鲳、鲶鱼还食水中死亡的鱼虾等动物的尸体及内脏，也是清除水体污染的帮手。  (3)白鲢  它被称为“水体过滤器”。适当套养白鲢可充分利用水体中的浮游生物等天然饵料资源，控制水体肥度，从而调节水质。同时，白鲢还是鱼类缺氧浮头的“预报员”，当白鲢出现浮头时则说明水体已开始缺氧，应及时采取增氧措施。  (4)草鱼、鳊鱼、团头鲂  它被称为水中“卫生员”。当水体水生植物过多或杂草较多的情况下，可适当套养草鱼、鳊鱼或团头鲂，这样既可净化养殖环境，又可利用天然饵料增加鱼产量。团头鲂还吃草鱼吃剩的碎草屑，鳊鱼还喜食水中的藻类。 &nbsp;','http://127.0.0.1:8080/tngou/news/170160')";
      
		sql=StringUtils.replace(sql, "'", "\"");
		System.err.println(sql);
		try {
			CCJSqlParserManager manager = new CCJSqlParserManager();
			manager.parse(new StringReader(sql));
			
		} catch (JSQLParserException e) {
			

			e.printStackTrace();
		}
	
	}
  
}
